#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""数据库迁移脚本 - 添加新字段"""

import sys
import sqlite3
from pathlib import Path

# 添加路径
sys.path.insert(0, str(Path(__file__).parent))

from src.database import db


def migrate_database():
    """执行数据库迁移"""
    print("=" * 60)
    print("  🔄 开始数据库迁移")
    print("=" * 60)
    
    conn = db.get_connection()
    cursor = conn.cursor()
    
    try:
        # 检查test_cases表是否有title字段
        cursor.execute("PRAGMA table_info(test_cases)")
        columns = {row['name'] for row in cursor.fetchall()}
        
        migrations = []
        
        # 迁移test_cases表
        if 'title' not in columns:
            print("\n📝 添加test_cases.title字段...")
            cursor.execute("ALTER TABLE test_cases ADD COLUMN title TEXT")
            migrations.append("test_cases.title")
            print("✅ 已添加test_cases.title字段")
        
        if 'generated_at' not in columns:
            print("\n📝 添加test_cases.generated_at字段...")
            cursor.execute("ALTER TABLE test_cases ADD COLUMN generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
            migrations.append("test_cases.generated_at")
            print("✅ 已添加test_cases.generated_at字段")
        
        # 检查code_analyses表
        cursor.execute("PRAGMA table_info(code_analyses)")
        columns = {row['name'] for row in cursor.fetchall()}
        
        # 迁移code_analyses表
        if 'projects_info' not in columns:
            print("\n📝 添加code_analyses.projects_info字段...")
            cursor.execute("ALTER TABLE code_analyses ADD COLUMN projects_info TEXT")
            migrations.append("code_analyses.projects_info")
            print("✅ 已添加code_analyses.projects_info字段")
        
        if 'started_at' not in columns:
            print("\n📝 添加code_analyses.started_at字段...")
            cursor.execute("ALTER TABLE code_analyses ADD COLUMN started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
            migrations.append("code_analyses.started_at")
            print("✅ 已添加code_analyses.started_at字段")
        
        # 提交更改
        conn.commit()
        
        print("\n" + "=" * 60)
        if migrations:
            print(f"  ✅ 迁移完成！共添加 {len(migrations)} 个字段")
            for field in migrations:
                print(f"     - {field}")
        else:
            print("  ℹ️  数据库已是最新版本，无需迁移")
        print("=" * 60)
        
    except Exception as e:
        conn.rollback()
        print(f"\n❌ 迁移失败: {e}")
        print("=" * 60)
        return False
    
    finally:
        conn.close()
    
    return True


def verify_migration():
    """验证迁移结果"""
    print("\n" + "=" * 60)
    print("  🔍 验证迁移结果")
    print("=" * 60)
    
    conn = db.get_connection()
    cursor = conn.cursor()
    
    try:
        # 检查test_cases表
        cursor.execute("PRAGMA table_info(test_cases)")
        test_cases_columns = {row['name'] for row in cursor.fetchall()}
        
        required_test_cases = {'id', 'project_id', 'title', 'content', 'raw_content', 
                               'status', 'version', 'generated_at', 'created_at', 'updated_at'}
        
        test_cases_ok = required_test_cases.issubset(test_cases_columns)
        status = "✅" if test_cases_ok else "❌"
        print(f"\ntest_cases表: {status}")
        if not test_cases_ok:
            missing = required_test_cases - test_cases_columns
            print(f"  缺少字段: {missing}")
        
        # 检查code_analyses表
        cursor.execute("PRAGMA table_info(code_analyses)")
        code_analyses_columns = {row['name'] for row in cursor.fetchall()}
        
        required_code_analyses = {'id', 'project_id', 'test_case_id', 'mr_url', 
                                  'projects_info', 'analysis_result', 'status', 
                                  'started_at', 'created_at', 'updated_at'}
        
        code_analyses_ok = required_code_analyses.issubset(code_analyses_columns)
        status = "✅" if code_analyses_ok else "❌"
        print(f"code_analyses表: {status}")
        if not code_analyses_ok:
            missing = required_code_analyses - code_analyses_columns
            print(f"  缺少字段: {missing}")
        
        all_ok = test_cases_ok and code_analyses_ok
        
        print("\n" + "=" * 60)
        if all_ok:
            print("  🎉 验证通过！所有字段都已正确添加")
        else:
            print("  ⚠️  验证失败，请检查")
        print("=" * 60)
        
        return all_ok
        
    except Exception as e:
        print(f"\n❌ 验证失败: {e}")
        return False
    
    finally:
        conn.close()


def main():
    """主函数"""
    # 执行迁移
    if not migrate_database():
        return 1
    
    # 验证迁移
    if not verify_migration():
        return 1
    
    return 0


if __name__ == "__main__":
    sys.exit(main())

